﻿using System.Collections.Generic;
using System.Data;
using System.Text;
using Utils;
using DataHelper;

namespace DAL
{
    public class EmpReInDao
    {
        #region 返聘和复职

        public bool Insert(SortedDictionary<string, string> sdList, SortedDictionary<string, string> hList)
        {
            StringBuilder builder = new StringBuilder();
            string ttl = sdList["TestTimeLong"];

            if (sdList["IsInsurance"] == "Y") //如果需要同步保险
            {
                builder.Append("Update Admin_Insurance Set Statu='Y',EndTime=null,StatuTime='" + System.DateTime.Now.ToString("yyyy-MM-dd") + "' Where Statu<>'Y' and EmpCode='" + hList["EmpCode"] + "';");
                if (hList["SynInsurance"] == "Y") //同步
                    builder.Append("Update a Set a.SafetyAmount=b.EmpPay from  Admin_Employee a, Admin_Insurance b Where a.EmpCode=b.EmpCode and  a.EmpCode='" + hList["EmpCode"] + "';");
                else if (hList["SynInsurance"] == "N") //不同步
                    builder.Append("Update Admin_Employee Set SafetyAmount=1  Where EmpCode='" + hList["EmpCode"] + "';");
                else //不使用
                    builder.Append("Update Admin_Employee Set SafetyAmount=0  Where EmpCode='" + hList["EmpCode"] + "';");
            }
            if (sdList["ReInTypeName"] == "返聘")
            {
                if (ttl == "0")
                    builder.Append("Update Admin_Employee Set StateID=" + sdList["StateID"] + ",DutyID=" + hList["DutyID"] + ",DeptID=" + hList["DeptID"] + ",AreaID=" + hList["AreaID"] + ",JoinTime='" + sdList["ReInTime"] + "',LeaveTime=null,ReMark='返聘',TestEndTime='" + sdList["ReInTime"] + "',TestTimeLong=" + ttl + "  Where EmpCode='" + hList["EmpCode"] + "';");
                else
                    builder.Append("Update Admin_Employee Set StateID=" + sdList["StateID"] + ",DutyID=" + hList["DutyID"] + ",DeptID=" + hList["DeptID"] + ",AreaID=" + hList["AreaID"] + ",JoinTime='" + sdList["ReInTime"] + "',LeaveTime=null,ReMark='返聘',TestEndTime=null,TestTimeLong=" + ttl + "  Where EmpCode='" + hList["EmpCode"] + "';");
            }
            else //复职
                //TestEndTime='" + ttt + "', 复职入职日期不变，转正不变 计算工龄工资时间不变
                 builder.Append("Update Admin_Employee Set StateID=" + sdList["StateID"] + ",DutyID=" + hList["DutyID"] + ",DeptID=" + hList["DeptID"] + ",AreaID=" + hList["AreaID"] + ",LeaveTime=null,ReMark='复职'  Where EmpCode='" + hList["EmpCode"] + "';");
            
            builder.Append("update Sys_Account set State='Y'  Where EmpCode='" + hList["EmpCode"] + "';");
            builder.Append("Update Admin_Emp_Leave Set Statu='N',Memo2='" + sdList["ReInTime"] + "'  Where EmpCode='" + hList["EmpCode"] + "' and Statu='Y';");
            builder.Append("Insert into Admin_Emp_ReIn (EmpCode,EmpName,OldDeptName,OldDutyName,OldAreaName,StateID,NewDeptName,NewDutyName,NewAreaName,ReInTime,ReInTypeName,TestTimeLong,Account,Memo,IsInsurance) values ");
            builder.Append("('" + sdList["EmpCode"] + "','" + sdList["EmpName"] + "','" + sdList["OldDeptName"] + "','" + sdList["OldDutyName"] + "','" + sdList["OldAreaName"] + "','" + sdList["StateID"] + "'");
            builder.Append(",'" + sdList["NewDeptName"] + "','" + sdList["NewDutyName"] + "','" + sdList["NewAreaName"] + "','" + sdList["ReInTime"] + "','" + sdList["ReInTypeName"] + "'," + ttl + ",'" + sdList["Account"] + "','" + sdList["Memo"] + "','" + sdList["IsInsurance"] + "');");
            return SQLHelper.ExecuteSqlWithTransaction(builder.ToString());
            //  return new BuiltSQL().InsertTable(tableName, sdList);
        }


        public bool Update(SortedDictionary<string, string> sdList, SortedDictionary<string, string> hList)
        {
            StringBuilder builder = new StringBuilder();
            string ttl = sdList["TestTimeLong"];
            if (sdList["IsInsurance"] == "Y") //如果需要同步保险
            {
                builder.Append("Update Admin_Insurance Set Statu='Y',EndTime=null,StatuTime='" + System.DateTime.Now.ToString("yyyy-MM-dd") + "' Where Statu<>'Y' and EmpCode='" + hList["EmpCode"] + "';");
                if (hList["SynInsurance"] == "Y") //同步
                    builder.Append("Update a Set a.SafetyAmount=b.EmpPay from  Admin_Employee a, Admin_Insurance b Where a.EmpCode=b.EmpCode and  a.EmpCode='" + hList["EmpCode"] + "';");
                else if (hList["SynInsurance"] == "N") //不同步
                    builder.Append("Update Admin_Employee Set SafetyAmount=1  Where EmpCode='" + hList["EmpCode"] + "';");
                else //不使用
                    builder.Append("Update Admin_Employee Set SafetyAmount=0  Where EmpCode='" + hList["EmpCode"] + "';");
            }
            if (sdList["ReInTypeName"] == "返聘")
            {
                if (ttl == "0")
                    builder.Append("Update Admin_Employee Set StateID=" + sdList["StateID"] + ",DutyID=" + hList["DutyID"] + ",DeptID=" + hList["DeptID"] + ",AreaID=" + hList["AreaID"] + ",JoinTime='" + sdList["ReInTime"] + "',LeaveTime=null,ReMark='返聘',TestEndTime='" + sdList["ReInTime"] + "',TestTimeLong=" + ttl + "  Where EmpCode='" + hList["EmpCode"] + "';");
                else
                    builder.Append("Update Admin_Employee Set StateID=" + sdList["StateID"] + ",DutyID=" + hList["DutyID"] + ",DeptID=" + hList["DeptID"] + ",AreaID=" + hList["AreaID"] + ",JoinTime='" + sdList["ReInTime"] + "',LeaveTime=null,ReMark='返聘',TestEndTime=null,TestTimeLong=" + ttl + "  Where EmpCode='" + hList["EmpCode"] + "';");
            }
            else
                //TestEndTime='" + ttt + "', 复职入职日期不变，转正不变 计算工龄工资时间不变
                builder.Append("Update Admin_Employee Set StateID=" + sdList["StateID"] + ",DutyID=" + hList["DutyID"] + ",DeptID=" + hList["DeptID"] + ",AreaID=" + hList["AreaID"] + ",LeaveTime=null,ReMark='复职'  Where EmpCode='" + hList["EmpCode"] + "';");

            builder.Append("Update Admin_Emp_Leave Set Statu='N',Memo2='" + sdList["ReInTime"] + "'  Where EmpCode='" + hList["EmpCode"] + "' and Statu='Y';");
            builder.Append("Update Admin_Emp_ReIn set ");
            builder.Append("EmpCode='" + sdList["EmpCode"] + "',EmpName='" + sdList["EmpName"] + "',OldDeptName='" + sdList["OldDeptName"] + "',OldDutyName='" + sdList["OldDutyName"] + "',OldAreaName='" + sdList["OldAreaName"] + "',StateID=" + sdList["StateID"]);
            builder.Append(",NewDeptName='" + sdList["NewDeptName"] + "',NewDutyName='" + sdList["NewDutyName"] + "',NewAreaName='" + sdList["NewAreaName"] + "',ReInTime='" + sdList["ReInTime"] + "',ReInTypeName='" + sdList["ReInTypeName"] + "',TestTimeLong=" + ttl + ",Account='" + sdList["Account"] + "',Memo='" + sdList["Memo"] + "',IsInsurance= '" + sdList["IsInsurance"] + "'");
            builder.Append(" where ReInID=" + sdList["ReInID"]);
            return SQLHelper.ExecuteSqlWithTransaction(builder.ToString());

            // return new BuiltSQL().UpdateTable(tableName, sdList);
        }


        public bool DeleteOneInfo(string reInID)
        {
            return SQLHelper.ExecuteSqlWithTransaction("Delete from Admin_Emp_ReIn Where ReInID=" + reInID);
        }
        //获取一条记录
        public DataTable GetOneInfo(string reInID)
        {
            return SQLHelper.ExecuteSqlGetDrToDt("Select * from Admin_Emp_ReIn Where ReInID=" + reInID);
        }
        //职位变动列表SQL
        public string GetSQL(string serWord, string bTime, string eTime, string reInTypeName, string deptID)
        {
            StringBuilder strSQL = new StringBuilder("Select a.*,b.Name as StateName From Admin_Emp_ReIn a inner join Admin_Work_State b on a.StateID=b.ID Where a.OldDutyName<>'售货员' ");
            if (!string.IsNullOrWhiteSpace(serWord) && serWord != "null")
                strSQL.Append(" And (a.EmpCode='" + serWord + "' Or a.EmpName Like '%" + serWord + "%')");
            else
            {
                if (!string.IsNullOrWhiteSpace(deptID) && deptID != "0")
                    strSQL.Append("and a.OldDeptName=(select top 1 DeptName From View_Emp WHERE deptID=" + deptID + " )");
                if (reInTypeName != "")
                    strSQL.Append(" and a.ReInTypeName ='" + reInTypeName + "' ");

                if (RegexRule.IsDate(bTime))
                    strSQL.Append(" And a.ReInTime>='" + bTime + "'");
                if (RegexRule.IsDate(eTime))
                    strSQL.Append(" And a.ReInTime<='" + eTime + "'");
            }
            return strSQL.ToString();
        }
        //列表
        public DataTable GetLists(string serWord, string bTime, string eTime, string changeTypeName, string deptID)
        {
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt(GetSQL(serWord, bTime, eTime, changeTypeName, deptID));
            return dt;
        }

        //根据类型，获取所有状态，显示在桌面上
        public DataTable GetStatuByType(string bTime, string eTime)
        {
            StringBuilder strSQL = new StringBuilder("Select ReInTypeName,count(ReInTypeName) as ReInNum From Admin_Emp_ReIn Where OldDutyName<>'售货员' ");
            if (RegexRule.IsDate(bTime))
                strSQL.Append(" And ReInTime>='" + bTime + "'");
            if (RegexRule.IsDate(eTime))
                strSQL.Append(" And ReInTime<='" + eTime + "'");

            strSQL.Append(" group by ReInTypeName order by count(ReInTypeName) desc ");
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL.ToString());
        }

        #endregion
    }
}
